﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FifaChamps.API.Data;

namespace FifaChamps.API.Data
{
    public static partial class DataManager
    {
        public static int Add_Season(int stageid, string name, DateTime? date, string venue) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Add_Season(stageid, name, date, venue).First();
                return result.SeasonID.Value;
            }
        }

        public static int Update_Season(int seasonid, int stageid, string name, DateTime? date, string venue) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Update_Season(seasonid, stageid, name, date, venue).First();
                return result.SeasonID.Value;
            }
        }

        public static Season Get_Season(int seasonID) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Seasons.Where(sea => sea.ID == seasonID).First();
                return new Season(result.ID, result.StageID, result.Name, result.Date, result.Venue);
            }
        }

        public static List<Season> Get_Seasons(int stageID)
        {
            List<Season> seasons = new List<Season>();
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Seasons.Where(sea => sea.StageID == stageID).ToList();
                result.ForEach(r => seasons.Add(new Season(r.ID, r.StageID, r.Name, r.Date, r.Venue)));
            }
            return seasons;
        }

        public static int Add_SeasonPlayer(int seasonID, int playerID) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Add_SeasonPlayer(seasonID, playerID, default(int?), default(int?), default(int?), default(int?), default(int?)).First();
                return result.SeasonPlayerID.Value;
            }
        }

        public static SeasonPlayer Get_SeasonPlayer(int seasonID, int playerID) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Seasons.Where(ps => ps.SeasonID == seasonID && ps.PlayerID == playerID).First();
                return Get_SeasonPlayer(result.ID);
            }
        } 

        public static SeasonPlayer Get_SeasonPlayer(int season_playerid) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Seasons.Where(ps => ps.ID == season_playerid).First();
                return new SeasonPlayer(result.ID, result.SeasonID, result.PlayerID, result.Position, result.Points, result.GamesPlayed, result.GoalsFor, result.GoalsAgainst);
            }
        }

        public static int Update_SeasonPlayer(int seasonID, int playerID, int points, int gamesPlayed, int goalsFor, int goalsAgainst, int position) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Seasons.Where(sp => sp.SeasonID == seasonID && sp.PlayerID == playerID).First().ID;
                return Update_SeasonPlayer(result, points, gamesPlayed, goalsFor, goalsAgainst, position);
            }
        }

        public static int Update_SeasonPlayer(int season_playerid, int points, int gamesPlayed, int goalsFor, int goalsAgainst, int position) 
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Update_SeasonPlayer(season_playerid, position, points, gamesPlayed, goalsFor, goalsAgainst).First();
                return result.SeasonPlayerID.Value;
            }
        }

        public static List<SeasonFixture> Get_SeasonFixtures(int seasonID) 
        {
            Dictionary<int, SeasonFixture> list = new Dictionary<int, SeasonFixture>();

            using (var db = DataManager.FifaDB())
            {
                var result = (from ps in db.FCT_Player_Seasons
                              join psr in db.FCT_PlayerSeason_FixtureResults
                              on ps.ID equals psr.PlayerSeasonID
                              join r in db.FCT_Fixture_Results
                              on psr.FixtureResultID equals r.ID
                              join f in db.FCT_Fixtures
                              on r.FixtureID equals f.ID
                              where ps.SeasonID == seasonID
                              select new
                              {
                                  playerseason_resultid = psr.ID,
                                  playerseasonid = psr.PlayerSeasonID,
                                  fixtureresultid = r.ID,
                                  resulttype = r.ResultTypeID,
                                  goalsfor = r.GoalsFor,
                                  goalsagainst = r.GoalsAgainst,
                                  fixtureid = f.ID,
                                  fixturename = f.FixtureName,
                                  home = r.Home
                              });

                foreach (var item in result.ToList())
                {
                    SeasonFixture season_fixture;
                    if (list.ContainsKey(item.fixtureid))
                    {
                        season_fixture = list[item.fixtureid];
                    }
                    else
                    {
                        season_fixture = new SeasonFixture(item.fixtureid, item.fixturename);
                        list.Add(item.fixtureid, season_fixture);
                    }

                    season_fixture.Results.Add(new SeasonResult(item.playerseason_resultid, item.playerseasonid, item.fixtureresultid, item.resulttype, item.fixtureid, item.goalsfor, item.goalsagainst, item.home));
                }
            }

            return list.Values.ToList();
        }

        public static SeasonFixture Get_SeasonFixture(int fixtureID) 
        {
            SeasonFixture season_fixture = null;
            using (var db = DataManager.FifaDB())
            {
                var result = (from ps in db.FCT_Player_Seasons
                              join psr in db.FCT_PlayerSeason_FixtureResults
                              on ps.ID equals psr.PlayerSeasonID
                              join r in db.FCT_Fixture_Results
                              on psr.FixtureResultID equals r.ID
                              join f in db.FCT_Fixtures
                              on r.FixtureID equals f.ID
                              where f.ID == fixtureID
                              select new
                              {
                                  playerseason_resultid = psr.ID,
                                  playerseasonid = psr.PlayerSeasonID,
                                  fixtureresultid = r.ID,
                                  resulttype = r.ResultTypeID,
                                  goalsfor = r.GoalsFor,
                                  goalsagainst = r.GoalsAgainst,
                                  fixtureid = f.ID,
                                  fixturename = f.FixtureName,
                                  home = r.Home
                              }).ToList();
                

                foreach (var item in result.ToList())
                {
                    if (season_fixture == null) season_fixture = new SeasonFixture(item.fixtureid, item.fixturename);                        
                    season_fixture.Results.Add(new SeasonResult(item.playerseason_resultid, item.playerseasonid, item.fixtureresultid, item.resulttype, item.fixtureid, item.goalsfor, item.goalsagainst, item.home));
                }
            }

            return season_fixture;
        }
        
    }
}
